-- 1.为xs表的学分列设置默认值为18
USE "XSGL";
ALTER TABLE "XS"
    ADD DEFAULT 18 FOR "总学分";
-- 2.为cj表设置标识列（列名自己起），起始行为2001，步长为1
USE "XSGL";
ALTER TABLE "CJ"
    ADD "AAAA" INT IDENTITY (2001,1);
CREATE UNIQUE INDEX "CJ_AAAA_UINDEX"
    ON "CJ" ("AAAA")
ALTER TABLE "CJ"
    DROP CONSTRAINT "PK_CJ"
ALTER TABLE "CJ"
    ADD CONSTRAINT "CJ_PK"
        PRIMARY KEY NONCLUSTERED ("AAAA")
ALTER TABLE "CJ"
    ADD CONSTRAINT "PK_CJ"
        UNIQUE CLUSTERED ("学号", "课程号")
-- 3.为xs表的性别列增加约束，让性别取值为男或者女
USE "XSGL";
ALTER TABLE "XS"
    ADD CONSTRAINT "性别" CHECK ("性别" = '男' OR "性别" = '女')
-- 4.定义check约束，要求学生学号sno必须为9位数字字符，且不能以0开头，第二三位皆为0
USE "XSGL";
ALTER TABLE "XS"
    DROP CONSTRAINT "CHECK_学号";
ALTER TABLE "XS"
    ADD CONSTRAINT "CHECK_学号" CHECK ("学号" LIKE '[1-9]00[0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
-- 5.要求学生表的学生姓名长度在2-8之间
USE "XSGL";
ALTER TABLE "XS"
    ADD CONSTRAINT "CHECK_姓名" CHECK (LEN("姓名") BETWEEN 2 AND 8)
-- 6.给xs表添加属性列“身份证号码”，并为其添加约束为18位（首字符不能为0的数字，最后一位可以是1-9数字或者X，其余位均为0-9的数字）
USE "XSGL";
ALTER TABLE "XS"
    ADD CONSTRAINT "CHECK_身份证" CHECK ("身份证" LIKE
                                      '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][1-9X]')
-- 7.给cj表增加级联删除和拒绝修改的学号和课程号的外码约束。
USE "XSGL";
ALTER TABLE "CJ"
    DROP CONSTRAINT "FK_CJ_CJ"
ALTER TABLE "CJ"
    DROP CONSTRAINT "FK_CJ_XS"
ALTER TABLE "CJ"
    ADD CONSTRAINT "FK_CJ_CJ"
        FOREIGN KEY ("课程号") REFERENCES "KC"
            ON DELETE CASCADE
            ON UPDATE NO ACTION
ALTER TABLE "CJ"
    ADD CONSTRAINT "FK_CJ_XS"
        FOREIGN KEY ("学号") REFERENCES "XS"
            ON DELETE CASCADE
            ON UPDATE NO ACTION
-- 8.创建借书表：lendt(bno（索书号）,sno（学号）,ldate（借阅日期）,rdate（应还日期），relend（是否续借））
-- --请为该表增加四个约束条件：
-- --（1）增加主码约束（bno,sno）
-- --（2）为借阅日期增加默认值约束，默认值为当前日期
-- --（3）为应还日期增加默认值约束，默认值为当前日期+30天
-- --（4）为是否续借增加默认值约束，默认值为否
USE "XSGL";
CREATE TABLE "LENDT"
(
    "BO"     CHAR(20),
    "SNO"    CHAR(10),
    "LDATE"  DATETIME DEFAULT (GETDATE()),
    "RDATE"  DATETIME DEFAULT (DATEADD(DAY, 30, GETDATE())),
    "RELEND" NCHAR(2) DEFAULT ('否'),
    PRIMARY KEY ("BO", "SNO")
);
-- 9.参照书上167页的5.6节断言完成下列操作：
-- （1）限定每个专业的选课门数不能超过10门
USE "XSGL";
CREATE TRIGGER "INSERT_选课门数"
    ON "CJ"
    AFTER INSERT
    AS
    IF ((
            SELECT MAX("选课门数")
                FROM (
                         SELECT COUNT(DISTINCT "CJ"."课程号") AS "选课门数"
                             FROM "CJ"
                                      INNER JOIN "XS" ON "CJ"."学号" = "XS"."学号"
                             GROUP BY "XS"."专业"
                             HAVING "XS"."专业" IN (
                                                     SELECT "XS"."专业"
                                                         FROM "INSERTED"
                                                                  JOIN "XS" ON "INSERTED"."学号" = "XS"."学号"
                                                 )
                     ) AS "AAA"
        ) >= 10)
            BEGIN
                PRINT ('每个专业的选课门数不`能超过10门')
                ROLLBACK TRANSACTION
            END
GO
--限定每个学生至少要选修6门课程
USE "XSGL";
CREATE TRIGGER "DELETE_选课门数"
    ON "CJ"
    AFTER DELETE
    AS
    IF ((
            SELECT MIN("课程门数")
                FROM (
                         SELECT COUNT(DISTINCT "CJ"."课程号") AS "课程门数"
                             FROM "CJ"
                             GROUP BY "CJ"."学号"
                             HAVING "CJ"."学号" IN (
                                                     SELECT "学号"
                                                         FROM "INSERTED"
                                                 )
                     ) AS "AAA"
        ) < 6)
            BEGIN
                PRINT ('每个专业的选课门数不`能超过10门')
                ROLLBACK TRANSACTION
            END
GO
-- 10.设计触发器实现：如果一个学生的专业是网络工程专业，那么他的总学分不得少于22分，也就是如果你插入一个新的网络工程专业的学生或者更新这个专业的学生的学分少于22分的话，就将其改为22学分。
USE "XSGL";
CREATE TRIGGER "INSERT_OR_UPDATE_总学分"
    ON "XS"
    AFTER INSERT , UPDATE
    AS IF UPDATE("总学分")
        BEGIN
            UPDATE "XS"
            SET "总学分"=CASE WHEN "总学分" >= 22 THEN "总学分" ELSE 22 END
                WHERE "专业" = '网络工程'
                  AND "学号" IN (
                                  SELECT "学号"
                                      FROM "INSERTED"
                              )
        END
GO
-- 11.建立教师表（教工编号，11姓名，专业，职称，工资）和
-- 工资变化表（教工编号，原工资，新工资），设计触发器实现教授的工资不得低于4000元，如果低于4000元则自动改为4000元。
-- 并且所有员工如果员工的工资发生变化则向工资变化表插入一条记录，包含教工编号，原工资，新工资。
USE "XSGL";
CREATE TABLE "教师表"
(
    "教工编号" BIGINT PRIMARY KEY NOT NULL IDENTITY (1,1),
    "姓名"   NVARCHAR(10)       NOT NULL,
    "专业"   NVARCHAR(10),
    "职称"   NVARCHAR(10),
    "工资"   FLOAT
)
CREATE TABLE "工资变化表"
(
    "教工编号" BIGINT FOREIGN KEY REFERENCES "教师表" ("教工编号") NOT NULL,
    "原工资"  FLOAT,
    "现工资"  FLOAT
)
CREATE TRIGGER "INSERT_OR_UPDATE_教授的工资"
    ON "教师表"
    AFTER INSERT, UPDATE
    AS IF UPDATE("工资") OR UPDATE("职称")
        BEGIN
            UPDATE "教师表"
            SET "工资"=CASE WHEN "工资" >= 4000 THEN "工资" ELSE 4000 END
                WHERE "职称" = '教授'
                  AND "教工编号" IN (
                                    SELECT "教工编号"
                                        FROM "INSERTED"
                                )
        END
GO
CREATE TRIGGER "UPDATE_工资"
    ON "教师表"
    AFTER UPDATE
    AS IF UPDATE("工资")
        BEGIN
            INSERT
                INTO "工资变化表"("教工编号", "原工资", "现工资")
                    (
                        SELECT "DELETED"."教工编号" AS "教工编号", "DELETED"."工资" AS "原工资", "INSERTED"."工资" AS "现工资"
                            FROM "INSERTED" INNER JOIN "DELETED" ON "DELETED"."教工编号" = "INSERTED"."教工编号"
                )
        END
GO
INSERT
    INTO "XSGL"."DBO"."教师表"
        ("姓名", "专业", "职称", "工资")
    VALUES
        ('123', '123', '132', 123),
        ('123', '123', '教授', 123),
        ('123', '456', '132', 456);
UPDATE "教师表"
SET "工资" = 100
    WHERE "教工编号" = 3;
UPDATE "教师表"
SET "工资" = 789
    WHERE "职称" = '132';
-- 12.设计触发器实现如果一个学生转专业了，那么输出一条信息显示该学生各门课程的平均分，但是如果该学生是计算机中澳专业则不允许转专业。
USE "XSGL";
CREATE TRIGGER "UPDATE_专业"
    ON "XS"
    AFTER UPDATE
    AS IF UPDATE("专业")
        BEGIN
            DECLARE "CUR" CURSOR FOR SELECT "INSERTED"."学号" AS "学号", "INSERTED"."专业" AS "新专业", "DELETED"."专业" AS "原专业"
                                         FROM "INSERTED" INNER JOIN "DELETED" ON "INSERTED"."学号" = "DELETED"."学号";
            DECLARE @学号 CHAR(10),@新专业 CHAR(16),@原专业 CHAR(16)
            OPEN "CUR"
            FETCH NEXT FROM "CUR" INTO @学号,@新专业,@原专业
            WHILE @@FETCH_STATUS = 0
                BEGIN
                    IF @新专业 = '计算机中澳' OR @原专业 = '计算机中澳'
                            BEGIN
                                RAISERROR ('计算机中澳不能呢个转专业',1,1)
                                ROLLBACK
                                BREAK
                            END
                    DECLARE @平均分 NUMERIC(18)
                    SELECT @平均分 = AVG("成绩")
                        FROM "CJ"
                        WHERE "学号" = @学号
                    PRINT @学号 + '的平均分是' + CONVERT(NVARCHAR(10), @平均分);
                    FETCH NEXT FROM "CUR" INTO @学号,@新专业,@原专业
                END
            CLOSE "CUR"
            DEALLOCATE "CUR"

        END
GO

UPDATE "XSGL"."DBO"."XS"
SET "专业" = N'计算机中澳        '
    WHERE "学号" LIKE N'2006030316'

UPDATE "XSGL"."DBO"."XS"
SET "专业" = N'计算机'
    WHERE "专业" = '计算机'

UPDATE "XSGL"."DBO"."XS"
SET "专业" = N'计算机中澳'
    WHERE "专业" = '计算机'
-- 13.设计触发器实现每门课的选课人数不能超过60人。
USE "XSGL";
CREATE TRIGGER "INSERT_选课"
    ON "CJ"
    AFTER INSERT
    AS
BEGIN
    IF ((
            SELECT MAX("选课人数")
                FROM (
                         SELECT COUNT("学号") AS "选课人数"
                             FROM "CJ"
                             WHERE "课程号" IN (
                                                SELECT "课程号"
                                                    FROM "INSERTED"
                                            )
                     ) AS "AAA"
        ) >= 60)
            BEGIN
                PRINT ('选课人数不能超过60人')
                ROLLBACK TRANSACTION
            END
END
GO
-- 14.设计触发器实现如果成绩被修改了20分以上，则输出提示信息“修改成绩超过20分，请慎重”，并输出该学生的选课记录。
USE "XSGL";
CREATE TRIGGER "UPDATE_成绩"
    ON "CJ"
    AFTER UPDATE
    AS
BEGIN
    IF UPDATE("成绩")
            BEGIN
                DECLARE "CUR" CURSOR FOR SELECT "INSERTED"."学号", "INSERTED"."课程号", "INSERTED"."成绩" AS "新成绩", "DELETED"."成绩" AS "原成绩"
                                             FROM "INSERTED" INNER JOIN "DELETED" ON "DELETED"."学号" = "INSERTED"."学号" AND "DELETED"."课程号" = "INSERTED"."课程号"
                DECLARE @学号 CHAR(10),@课程号 CHAR(10),@原成绩 NUMERIC(18),@新成绩 NUMERIC(18);
                OPEN "CUR"
                FETCH NEXT FROM "CUR" INTO @学号,@课程号,@新成绩,@原成绩
                WHILE @@FETCH_STATUS = 0
                    BEGIN
                        IF (ABS(@新成绩 - @原成绩) > 20)
                                BEGIN
                                    PRINT '修改' + @学号 + '的' + @课程号 + '成绩' + CONVERT(NVARCHAR(10), ABS(@新成绩 - @原成绩)) + '分,超过20分，请慎重'
                                END
                        FETCH NEXT FROM "CUR" INTO @学号,@课程号,@新成绩,@原成绩
                    END
                CLOSE "CUR"
                DEALLOCATE "CUR"
            END
END
GO
UPDATE "XSGL"."DBO"."CJ"
SET "成绩" = 0
    WHERE "课程号" = 'J002'
-- 15.如果一门课的学分发生了修改， 则所有选修该课程并且及格的学生的总学分要做相应的修改，并输出学号，姓名，原总学分，新总学分。
USE "XSGL";
CREATE TRIGGER "UPDATE_学分"
    ON "KC"
    AFTER UPDATE AS
BEGIN
    IF UPDATE("学分")
            BEGIN
                DECLARE "CUR_KC" CURSOR FOR SELECT "INSERTED"."课程号" AS "课程号", "INSERTED"."学分" AS "新学分", "DELETED"."学分" AS "原学分"
                                                FROM "INSERTED" INNER JOIN "DELETED" ON "INSERTED"."课程号" = "DELETED"."课程号";
                DECLARE @课程号 CHAR(10),@新学分 SMALLINT,@原学分 SMALLINT;
                OPEN "CUR_KC";

                FETCH NEXT FROM "CUR_KC" INTO @课程号,@新学分,@原学分
                WHILE @@FETCH_STATUS = 0
                    BEGIN
                        DECLARE "CUR_XS" CURSOR FOR SELECT "CJ"."学号"
                                                        FROM "CJ"
                                                        WHERE "CJ"."成绩" >= 60
                                                          AND "CJ"."课程号" = @课程号
                        OPEN "CUR_XS";
                        DECLARE @学号 CHAR(10);
                        FETCH NEXT FROM "CUR_XS" INTO @学号
                        WHILE @@FETCH_STATUS = 0
                            BEGIN
                                DECLARE @原总学分 SMALLINT,@新总学分 SMALLINT,@姓名 NCHAR(10);
                                SELECT @原总学分 = "总学分", @新总学分 = "总学分" - @原学分 + @新学分, @姓名 = "姓名"
                                    FROM "XS"
                                    WHERE "XS"."学号" = @学号;
                                UPDATE "XS"
                                SET "总学分"=@新总学分
                                    WHERE "XS"."学号" = @学号;
                                PRINT '学号:' + @学号 + '姓名:' + @姓名 + '原总学分:' + CONVERT(NVARCHAR(10), @原总学分) + '新总学分:' + CONVERT(NVARCHAR(10), @新总学分)
                                FETCH NEXT FROM "CUR_XS" INTO @学号
                            END
                        CLOSE "CUR_XS";
                        DEALLOCATE "CUR_XS"
                        FETCH NEXT FROM "CUR_KC" INTO @课程号,@新学分,@原学分
                    END

                CLOSE "CUR_KC";
                DEALLOCATE "CUR_KC"
            END
END
GO
UPDATE "XSGL"."DBO"."KC"
SET "学分" = 100
    WHERE "课程号" LIKE N'A001      ' ESCAPE '#'
-- 16.针对northwind数据库实现触发器：每个员工每天处理订单的数量不能超过100，如果超出100个则拒绝处理，并提示“处理订单数量超出限额”
USE "NORTHWIND"
CREATE TRIGGER "INSERT_订单数量"
    ON "ORDERS"
    AFTER INSERT
    AS
BEGIN
    IF ((
            SELECT MAX("订单数量")
                FROM (
                         SELECT COUNT("ORDERID") AS "订单数量"
                             FROM "ORDERS"
                             GROUP BY "EMPLOYEEID"
                             HAVING "EMPLOYEEID" IN (
                                                        SELECT "EMPLOYEEID"
                                                            FROM "INSERTED"
                                                    )
                     ) AS "AAA"
        ) > 100)
            BEGIN
                PRINT ('处理订单数量超出限额')
                ROLLBACK TRANSACTION
            END
END
GO
-- 17.针对northwind数据库实现触发器：给orders表添加zje（订单总金额）属性列，要求保留两位小数，设置触发器实现当产生了新的订单明细之后将总金额更新到到订单表里。
USE "NORTHWIND"
ALTER TABLE "ORDERS"
    ADD "ZJE" NUMERIC(18, 2)

CREATE TRIGGER "INSERT_订单细节"
    ON "ORDER DETAILS"
    AFTER INSERT
    AS
BEGIN
    UPDATE "ORDERS"
    SET "ORDERS"."ZJE"=ISNULL("ZJE", 0) + (
                                              SELECT SUM("UNITPRICE" * "QUANTITY" * (1 - "DISCOUNT"))
                                                  FROM "INSERTED"
                                                  GROUP BY "INSERTED"."ORDERID"
                                                  HAVING "INSERTED"."ORDERID" = "ORDERS"."ORDERID"
                                          )
        WHERE "ORDERS"."ORDERID" IN (
                                        SELECT "INSERTED"."ORDERID"
                                            FROM "INSERTED"
                                    )
END
GO
-- 18.在课程表里添加一列：选课人数，设置触发器每门课如果有人选修了那么课程表里的选课人数相应修改，考虑批量插入选课记录的情况。
USE "XSGL";
ALTER TABLE "KC"
    ADD "选课人数" INT
UPDATE "KC"
SET "选课人数"=(
               SELECT COUNT("学号")
                   FROM "CJ"
                   WHERE "CJ"."课程号" = "KC"."课程号"
           )
CREATE TRIGGER "UPDATE_选课人数"
    ON "CJ"
    AFTER INSERT
    AS
BEGIN
    UPDATE "KC"
    SET "KC"."选课人数"+=(
                         SELECT COUNT("INSERTED"."学号")
                             FROM "INSERTED"
                             GROUP BY "INSERTED"."课程号"
                             HAVING "INSERTED"."课程号" = "KC"."课程号"
                     )
        WHERE "KC"."课程号" IN (
                                SELECT "课程号"
                                    FROM "INSERTED"
                            )
END
GO
INSERT
    INTO "XSGL"."DBO"."CJ"
        ("学号", "课程号", "成绩")
    VALUES
        (N'2006030101', N'J005', NULL),
        (N'2006030102', N'J005', NULL),
        (N'2006030103', N'J005', NULL)
-- 19.设置触发器实现如果学生表发生了插入、更新或者删除操作，请输出插入、更新或者删除的行数。
USE "XSGL";
CREATE TRIGGER "设置触发器实现如果学生表发生了插入、更新或者删除操作，请输出插入、更新或者删除的行数"
    ON "XS"
    AFTER INSERT,UPDATE,DELETE
    AS
    PRINT ('影响' + CONVERT(NVARCHAR(100), @@rowcount) + '行')
GO
CREATE TRIGGER "设置触发器实现如果学生表发生了插入操作，请输出插入行数"
    ON "XS"
    AFTER INSERT
    AS
    DECLARE
        @行数 INT
    SELECT @行数 = COUNT(*)
        FROM "INSERTED"
    PRINT ('插入' + CONVERT(NVARCHAR(100), @行数) + '行')
GO
CREATE TRIGGER "设置触发器实现如果学生表发生了更新操作，请输出更新的行数"
    ON "XS"
    AFTER UPDATE
    AS
    DECLARE
        @行数 INT
    SELECT @行数 = COUNT(*)
        FROM "INSERTED"
    PRINT ('更新' + CONVERT(NVARCHAR(100), @行数) + '行')
GO
CREATE TRIGGER "设置触发器实现如果学生表发生了删除操作，请输出删除的行数"
    ON "XS"
    AFTER DELETE
    AS
    DECLARE
        @行数 INT
    SELECT @行数 = COUNT(*)
        FROM "DELETED"
    PRINT ('删除' + CONVERT(NVARCHAR(100), @行数) + '行')
GO
